package com.iMusic.businessLogicLayer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.iMusic.integrationLayer.DBConnection;
import com.iMusic.integrationLayer.DBConnectionManager;

public class Inventory {

	static private Inventory instance = null;
	
	/*
	 * Get instance static method to retrieve the Inventory
	 */
	static public Inventory getInstance() {
		if (instance == null) {
			instance = new Inventory();
		}
		return instance;
		
	}
	
	/*
	 * Retrieves a list of all products
	 */
	public List<Product> getAllProducts() throws SQLException {
		return Product.searchByKeywords(null, null);
	}
	
	
	/*
	 * Deletes a product
	 */
	public void deleteProduct(int prodId) throws SQLException {
		
		DBConnectionManager dbConnMgr = DBConnectionManager.getDBConnectionManager();
		DBConnection dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
		
		String sqlStmt = "DELETE FROM Product WHERE ProductId = " + prodId;
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
	}
	
	/*
	 * Adds new product
	 */
	public void addNewProduct(Product prod) throws SQLException {
		
		DBConnectionManager dbConnMgr = DBConnectionManager.getDBConnectionManager();
		DBConnection dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
		
		int typeId = getProductTypeId(dbConn, prod.getTypeName());
		int categoryId = getProductCategoryId(dbConn, prod.getCategoryName());
		int prodId = getNextProductId(dbConn);
		
		if (prod.getTypeName().startsWith("MP3")) {
			prod.setUnitsInStock(-1);
		}
		
		String sqlStmt = "INSERT INTO Product (ProductId, ProductName, Description, TypeId, CategoryId, Price, UnitsInStock, CountryId) " +
							"VALUES (" + prodId + ", '" + prod.getName() + "', '" + prod.getDescription() + "', " +
							typeId + ", " + categoryId + ", " + prod.getPrice() + ", " + prod.getUnitsInStock() + ", 1)";
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
		
		
	}
	
	
	/*
	 * Updates product information
	 */
	public void updateProduct(Product prod) throws SQLException {
		
		DBConnectionManager dbConnMgr = DBConnectionManager.getDBConnectionManager();
		DBConnection dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
		
		int typeId = getProductTypeId(dbConn, prod.getTypeName());
		int categoryId = getProductCategoryId(dbConn, prod.getCategoryName());
		
		if (prod.getTypeName().startsWith("MP3")) {
			prod.setUnitsInStock(-1);
		}
		
		String sqlStmt = "UPDATE Product " +
							"SET ProductName = '" + prod.getName() + "', " +
							"Description = '" + prod.getDescription() + "', " +
							"TypeId = " + typeId + ", " +
							"CategoryId = " + categoryId + ", " +
							"Price = " + prod.getPrice() + ", " +
							"UnitsInStock = " + prod.getUnitsInStock() + " " +
							"WHERE ProductId = " + prod.getProductId();
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
		
		
	}
	
	/*
	 * Retrieves Type id
	 */
	private int getProductTypeId(DBConnection dbConn, String type) throws SQLException {
		int ret = -1;
		
		ResultSet rs = dbConn.query("SELECT TypeId FROM ProductType " +
										"WHERE TypeName = '" + type + "'");

		if (rs != null && rs.next()) {
			ret = rs.getInt("TypeId");
		}
		
		return ret;
	}
	
	/*
	 * Retrieves Category id
	 */
	private int getProductCategoryId(DBConnection dbConn, String category) throws SQLException {
		int ret = -1;
		
		ResultSet rs = dbConn.query("SELECT CategoryId FROM CategoryType " +
										"WHERE CategoryName = '" + category + "'");

		if (rs != null && rs.next()) {
			ret = rs.getInt("CategoryId");
		}
		
		return ret;
	}
	
	/*
	 * Retrieves next productId
	 */
	private int getNextProductId(DBConnection dbConn) throws SQLException {
		int ret = 1;
		
		ResultSet rs = dbConn.query("SELECT MAX(ProductId)+1 NextProductId FROM Product ");

		if (rs != null && rs.next()) {
			ret = rs.getInt("NextProductId");
		}
		
		return ret;
	}
}
